package com.bootcamp.s3_1013.database._1019_JBUtils.work;

import com.bootcamp.s3_1013.database._1019_JBUtils.City;
import com.bootcamp.s3_1013.database.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.SQLException;
import java.util.List;

/**
 * @program: myClassCode
 * @description: use dbutils to manipulate table
 * @author: Keyu Li
 * @create: 2021-10-19 20:57
 **/

public class Ex2_DBUtils {

    //对city表进行增删查改

    @Test
    public void updateTest() {
        // get query runner
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            // 新增城市郑州
            int update = queryRunner.update("insert into city values(7,'郑州',41)");
            // 修改城市长沙为常德
            int update1 = queryRunner.update("update city set name='常德' where name='长沙'");
            // 删除常德市
            int update2 = queryRunner.update("delete from city where name='常德'");
            System.out.printf("1-1-1?: %d-%d-%d",update,update1,update2);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    @Test
    public void beanHandlerTest() {
        // get query runner
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            City city = queryRunner.query("select * from city where id=?", new BeanHandler<>(City.class), 1);
            System.out.println("bean test:\n" + city);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    @Test
    public void beanListHandlerTest() {
        // get query runner
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            List<City> cityList = queryRunner.query("select * from  city", new BeanListHandler<>(City.class));
            System.out.println("bean list test:\n" + cityList);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    @Test
    public void cloumnListHandlerTest() {
        // get query runner
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<String> cityNames = null;
        try {
            cityNames = queryRunner.query("select name from city", new ColumnListHandler<String>());
            System.out.println("column list test:\n" + cityNames);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Test
    public void scalarHandlerTest() {
        // get query runner
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            Object query = queryRunner.query("select count(*) from city", new ScalarHandler<>());
            System.out.println(query);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }


}
